<?php

define('HOST','mysql13.000webhost.com');
define('DBNAME','a8259506_photodi');
define('USER','a8259506_fenwick');
define('PASSWORD','hetre2012');

class databaseHandler
{
	private $connectionHandler;
	
	public function __construct()
	{
		$this->connectionHandler = new mysqli(HOST, USER, PASSWORD, DBNAME);
		return;
	}
	
	public function __destruct()
	{
		$this->connectionHandler->close();
		return;
	}
	
	public function getEventsList()
	{
		$eventsArray = null;
		
		$request = "SELECT * from event";
		$events = $this->connectionHandler->query($request);
		while($row = $events->fetch_assoc())
		{
				
			$eventsArray[$row['event_id']]= $row['event_name'];
		}
		$events->close();
		return $eventsArray;
	}
	
	public function getImages()
	{
		return $this->getImagesForEventID("");
	}
	
	public function getImagesForEventID($eventId)
	{
		$imageArray = array();
		$request = "SELECT * from photo";
		if ($eventId != null && $eventId != ''){
			$request .= " WHERE event_id =" . $eventId;
		}
		$images = $this->connectionHandler->query($request);
		while($row = $images->fetch_assoc())
		{
			$imageArray[$row['photo_id']]= $row['photo_url'];
		}
		$images->close();
		return $imageArray;		
	}
	
	public function getImagesForCategoryID($categoryId)
	{
		$imageArray = array();
		$request = "
		SELECT * from 
			photocategory AS tableCat
			JOIN photo AS tablePhoto 
			ON (tableCat.photo_id = tablePhoto.photo_id)
			WHERE tableCat.category_id = ".$categoryId;
		$images = $this->connectionHandler->query($request);
		while($row = $images->fetch_assoc())
		{
			$imageArray[$row['photo_id']]= $row['photo_url'];
		}
		$images->close();
		return $imageArray;		
	}
	
	public function addEvent($eventName)
	{
		$request = "INSERT INTO `photodigme`.`event` ( `event_id` ,`event_name` ,`begindate` ,`enddate`)
		VALUES (NULL ,'".$eventName. "', NULL , NULL)";
		$response = $this->connectionHandler->query($request);
		if($response == true)
		{
			
		}else{
			echo "problem to execute ". $request;
		}
	}
	
	public function addImage($photo_url,$event_id)
	{
		if($photo_url!= null && $event_id != null && $photo_url!= '' && $event_id != ''){
			$request = "INSERT INTO `photodigme`.`photo` ( `photo_id` ,`photo_url` ,`event_id` ,`subevent_id`)
			VALUES (NULL ,'".$photo_url."',".$event_id.", NULL)";
			if($this->connectionHandler->query($request) == true)
			{
				return;
			}else{
				return null;
			}
		}
	}
	
	public function addCategory($categoryName)
	{
		$request = "INSERT INTO `photodigme`.`category` (`category_id`, `category_name`) VALUES (NULL, '".$categoryName."')";
		$response = $this->connectionHandler->query($request);
		if($response == true)
		{
			return;
		}else{
			return null;
		}
	}
	
	public function getCategories()
	{
		$categoriesArray = null;
		
		$request = "SELECT * FROM `photodigme`.`category`";
		$response = $this->connectionHandler->query($request);
		while($row = $response->fetch_assoc())
		{
				
			$categoriesArray[$row['category_id']]= $row['category_name'];
		}
		$response->close();
		return $categoriesArray;
	}
	
	public function linkPhotoCategroy($photoUrl, $categroyId)
	{
		if ($photoUrl != null && $photoUrl != ""){
			$request = "INSERT INTO `photodigme`.`photocategory` (`photoCategory_id`, `photo_id`, `category_id`) 
			SELECT 
			NULL,
			(SELECT photo_id FROM photo WHERE photo_url = '".$photoUrl."'),"
			.$categroyId;
		
			$response = $this->connectionHandler->query($request);
			if($response == true)
			{
				return;
			}else{
				return null;
			}
		}
		return;
	}
}

?>